package cn.micro.dc_excel.controller;

import cn.micro.dc_excel.tools.ToolsDaochu;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


@RestController
public class girlController {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public List daochu(String sql) {
        List list = jdbcTemplate.queryForList(sql);
/*

        System.out.println(list.size());

*/
        return list;

    }

    /**

     * 代理商管理

     * grz

     * status:0

     * dlsgl.do

     */
    @RequestMapping(value = "dlsgl.do")
    public void sjgldsh(HttpServletRequest req, HttpServletResponse res ,String id) throws Exception {
        HashMap<String, Object> map = new HashMap<>();
        map.put("0-0", "代理商账号");
        map.put("0-1", "代理商联系人");
        map.put("0-2", "创建时间");
        map.put("0-3", "联系方式");
        map.put("0-4", "负责辖区");
        map.put("0-5", "商家数量");
        map.put("0-6", "状态");
        String sql="";
        if(id.equals("0")&&id!=null){
        sql= "SELECT t.*\n" +
                    "        FROM\n" +
                    "        (\n" +
                    "        SELECT\n" +
                    "        a.id,\n" +
                    "        a.company_id,\n" +
                    "        a.office_id,\n" +
                    "        a.user_type,\n" +
                    "        a.login_name,\n" +
                    "        a.name,\n" +
                    "        a.tel,\n" +
                    "        a.login_ip,\n" +
                    "        a.login_date,\n" +
                    "        a.login_flag,\n" +
                    "        a.create_by,\n" +
                    "        a.create_date,\n" +
                    "        a.update_by,\n" +
                    "        a.update_date,\n" +
                    "        a.del_flag,\n" +
                    "        a.remarks,\n" +
                    "        g.local_name AS \"ext1\",\n" +
                    "        e.local_name AS \"ext2\",\n" +
                    "        f.local_name AS \"ext3\",\n" +
                    "        (SELECT COUNT(*)\n" +
                    "        FROM USER X LEFT JOIN user_info Y ON x.id = y.ext3\n" +
                    "        WHERE y.city = b.city AND y.merchant_name IS NOT NULL) AS \"userInfo.ext2\",\n" +
                    "        b.id AS \"userInfo.id\",\n" +
                    "        b.merchant_name AS \"userInfo.merchantName\",\n" +
                    "        b.merchant_contact AS \"userInfo.merchantContact\",\n" +
                    "        b.merchant_fenrun AS \"userInfo.merchantFenrun\",\n" +
                    "        b.merchant_classify AS \"userInfo.merchantClassify\",\n" +
                    "        b.classify_parent AS \"userInfo.classifyParent\",\n" +
                    "        b.province AS \"userInfo.province\",\n" +
                    "        b.city AS \"userInfo.city\",\n" +
                    "        b.district AS \"userInfo.district\",\n" +
                    "        g.local_name AS \"userInfo.provinceName\",\n" +
                    "        e.local_name AS \"userInfo.cityName\",\n" +
                    "        f.local_name AS \"userInfo.districtName\",\n" +
                    "        b.site AS \"userInfo.site\",\n" +
                    "        b.data_licence AS \"userInfo.dataLicence\",\n" +
                    "        b.data_franchise AS \"userInfo.dataFranchise\",\n" +
                    "        b.data_else AS \"userInfo.dataElse\",\n" +
                    "        b.agent_name AS \"userInfo.agentName\",\n" +
                    "        b.agent_tel AS \"userInfo.agentTel\",\n" +
                    "        b.agent_email AS \"userInfo.agentEmail\",\n" +
                    "        b.ext1 AS \"userInfo.ext1\",\n" +
                    "        b.ext3 AS \"userInfo.ext3\"\n" +
                    "        FROM USER a\n" +
                    "        LEFT JOIN user_info b ON a.id = b.ext3\n" +
                    "        LEFT JOIN user_role c ON c.user_id = a.id\n" +
                    "        LEFT JOIN regions g ON b.province = g.region_id\n" +
                    "        LEFT JOIN regions e ON b.city = e.region_id\n" +
                    "        LEFT JOIN regions f ON b.district = f.region_id\n" +
                    "        WHERE b.agent_name IS NOT NULL AND b.district IS NULL\n" +
                    "        AND c.role_id = 'a1'\n" +
                    "        GROUP BY a.id\n" +
                    "\n" +
                    "        UNION ALL\n" +
                    "\n" +
                    "        SELECT\n" +
                    "        a.id,\n" +
                    "        a.company_id,\n" +
                    "        a.office_id,\n" +
                    "        a.user_type,\n" +
                    "        a.login_name,\n" +
                    "        a.name,\n" +
                    "        a.tel,\n" +
                    "        a.login_ip,\n" +
                    "        a.login_date,\n" +
                    "        a.login_flag,\n" +
                    "        a.create_by,\n" +
                    "        a.create_date,\n" +
                    "        a.update_by,\n" +
                    "        a.update_date,\n" +
                    "        a.del_flag,\n" +
                    "        a.remarks,\n" +
                    "        g.local_name AS \"ext1\",\n" +
                    "        e.local_name AS \"ext2\",\n" +
                    "        f.local_name AS \"ext3\",\n" +
                    "        (SELECT COUNT(*)\n" +
                    "        FROM USER X LEFT JOIN user_info Y ON x.id = y.ext3\n" +
                    "        WHERE y.district = b.district AND y.merchant_name IS NOT NULL) AS \"userInfo.ext2\",\n" +
                    "        b.id AS \"userInfo.id\",\n" +
                    "        b.merchant_name AS \"userInfo.merchantName\",\n" +
                    "        b.merchant_contact AS \"userInfo.merchantContact\",\n" +
                    "        b.merchant_fenrun AS \"userInfo.merchantFenrun\",\n" +
                    "        b.merchant_classify AS \"userInfo.merchantClassify\",\n" +
                    "        b.classify_parent AS \"userInfo.classifyParent\",\n" +
                    "        b.province AS \"userInfo.province\",\n" +
                    "        b.city AS \"userInfo.city\",\n" +
                    "        b.district AS \"userInfo.district\",\n" +
                    "        g.local_name AS \"userInfo.provinceName\",\n" +
                    "        e.local_name AS \"userInfo.cityName\",\n" +
                    "        f.local_name AS \"userInfo.districtName\",\n" +
                    "        b.site AS \"userInfo.site\",\n" +
                    "        b.data_licence AS \"userInfo.dataLicence\",\n" +
                    "        b.data_franchise AS \"userInfo.dataFranchise\",\n" +
                    "        b.data_else AS \"userInfo.dataElse\",\n" +
                    "        b.agent_name AS \"userInfo.agentName\",\n" +
                    "        b.agent_tel AS \"userInfo.agentTel\",\n" +
                    "        b.agent_email AS \"userInfo.agentEmail\",\n" +
                    "        b.ext1 AS \"userInfo.ext1\",\n" +
                    "        b.ext3 AS \"userInfo.ext3\"\n" +
                    "        FROM USER a\n" +
                    "        LEFT JOIN user_info b ON a.id = b.ext3\n" +
                    "        LEFT JOIN user_role c ON c.user_id = a.id\n" +
                    "        LEFT JOIN regions g ON b.province = g.region_id\n" +
                    "        LEFT JOIN regions e ON b.city = e.region_id\n" +
                    "        LEFT JOIN regions f ON b.district = f.region_id\n" +
                    "        WHERE b.agent_name IS NOT NULL AND b.district IS NOT NULL\n" +
                    "        AND c.role_id = 'a1'\n" +
                    "        GROUP BY a.id\n" +
                    "\n" +
                    "        ) AS t\n" +
                    "        WHERE t.del_flag = 0\n";


        }else if(id.equals("2")&&id!=null){
            sql="SELECT t.*\n" +
                    "        FROM\n" +
                    "        ( \n" +
                    " \n" +
                    " SELECT\n" +
                    "        b.district ,\n" +
                    "        a.id,\n" +
                    "        a.company_id,\n" +
                    "        a.office_id,\n" +
                    "        a.user_type,\n" +
                    "        a.login_name,\n" +
                    "        a.name,\n" +
                    "        a.tel,\n" +
                    "        a.login_ip,\n" +
                    "        a.login_date,\n" +
                    "        a.login_flag,\n" +
                    "        a.create_by,\n" +
                    "        a.create_date,\n" +
                    "        a.update_by,\n" +
                    "        a.update_date,\n" +
                    "        a.del_flag,\n" +
                    "        a.remarks,\n" +
                    "        g.local_name AS \"ext1\",\n" +
                    "        e.local_name AS \"ext2\",\n" +
                    "        f.local_name AS \"ext3\",\n" +
                    "        (SELECT COUNT(*)\n" +
                    "        FROM USER X LEFT JOIN user_info Y ON x.id = y.ext3\n" +
                    "        WHERE y.city = b.city AND y.merchant_name IS NOT NULL) AS \"userInfo.ext2\",\n" +
                    "        b.id AS \"userInfo.id\",\n" +
                    "        b.merchant_name AS \"userInfo.merchantName\",\n" +
                    "        b.merchant_contact AS \"userInfo.merchantContact\",\n" +
                    "        b.merchant_fenrun AS \"userInfo.merchantFenrun\",\n" +
                    "        b.merchant_classify AS \"userInfo.merchantClassify\",\n" +
                    "        b.classify_parent AS \"userInfo.classifyParent\",\n" +
                    "        b.province AS \"userInfo.province\",\n" +
                    "        b.city AS \"userInfo.city\",\n" +
                    "        b.district AS \"userInfo.district\",\n" +
                    "        g.local_name AS \"userInfo.provinceName\",\n" +
                    "        e.local_name AS \"userInfo.cityName\",\n" +
                    "        f.local_name AS \"userInfo.districtName\",\n" +
                    "        b.site AS \"userInfo.site\",\n" +
                    "        b.data_licence AS \"userInfo.dataLicence\",\n" +
                    "        b.data_franchise AS \"userInfo.dataFranchise\",\n" +
                    "        b.data_else AS \"userInfo.dataElse\",\n" +
                    "        b.agent_name AS \"userInfo.agentName\",\n" +
                    "        b.agent_tel AS \"userInfo.agentTel\",\n" +
                    "        b.agent_email AS \"userInfo.agentEmail\",\n" +
                    "        b.ext1 AS \"userInfo.ext1\",\n" +
                    "        b.ext3 AS \"userInfo.ext3\"\n" +
                    "        FROM USER a\n" +
                    "        LEFT JOIN user_info b ON a.id = b.ext3\n" +
                    "        LEFT JOIN user_role c ON c.user_id = a.id\n" +
                    "        LEFT JOIN regions g ON b.province = g.region_id\n" +
                    "        LEFT JOIN regions e ON b.city = e.region_id\n" +
                    "        LEFT JOIN regions f ON b.district = f.region_id\n" +
                    "        WHERE b.agent_name IS NOT NULL AND b.district IS NULL\n" +
                    "        AND c.role_id = 'a1'\n" +
                    "        GROUP BY a.id\n" +
                    "\n" +
                    "      \n" +
                    "        ) AS t\n" +
                    "        WHERE t.del_flag = 0  \n" +
                    "       ";

        }else if(id.equals("3")&&id!=null){
            sql="SELECT t.*\n" +
                    "        FROM\n" +
                    "        (\n" +
                    "       \n" +
                    "\n" +
                    "        SELECT\n" +
                    "        b.district ,\n" +
                    "        a.id,\n" +
                    "        a.company_id,\n" +
                    "        a.office_id,\n" +
                    "        a.user_type,\n" +
                    "        a.login_name,\n" +
                    "        a.name,\n" +
                    "        a.tel,\n" +
                    "        a.login_ip,\n" +
                    "        a.login_date,\n" +
                    "        a.login_flag,\n" +
                    "        a.create_by,\n" +
                    "        a.create_date,\n" +
                    "        a.update_by,\n" +
                    "        a.update_date,\n" +
                    "        a.del_flag,\n" +
                    "        a.remarks,\n" +
                    "        g.local_name AS \"ext1\",\n" +
                    "        e.local_name AS \"ext2\",\n" +
                    "        f.local_name AS \"ext3\",\n" +
                    "        (SELECT COUNT(*)\n" +
                    "        FROM USER X LEFT JOIN user_info Y ON x.id = y.ext3\n" +
                    "        WHERE y.district = b.district AND y.merchant_name IS NOT NULL) AS \"userInfo.ext2\",\n" +
                    "        b.id AS \"userInfo.id\",\n" +
                    "        b.merchant_name AS \"userInfo.merchantName\",\n" +
                    "        b.merchant_contact AS \"userInfo.merchantContact\",\n" +
                    "        b.merchant_fenrun AS \"userInfo.merchantFenrun\",\n" +
                    "        b.merchant_classify AS \"userInfo.merchantClassify\",\n" +
                    "        b.classify_parent AS \"userInfo.classifyParent\",\n" +
                    "        b.province AS \"userInfo.province\",\n" +
                    "        b.city AS \"userInfo.city\",\n" +
                    "        b.district AS \"userInfo.district\",\n" +
                    "        g.local_name AS \"userInfo.provinceName\",\n" +
                    "        e.local_name AS \"userInfo.cityName\",\n" +
                    "        f.local_name AS \"userInfo.districtName\",\n" +
                    "        b.site AS \"userInfo.site\",\n" +
                    "        b.data_licence AS \"userInfo.dataLicence\",\n" +
                    "        b.data_franchise AS \"userInfo.dataFranchise\",\n" +
                    "        b.data_else AS \"userInfo.dataElse\",\n" +
                    "        b.agent_name AS \"userInfo.agentName\",\n" +
                    "        b.agent_tel AS \"userInfo.agentTel\",\n" +
                    "        b.agent_email AS \"userInfo.agentEmail\",\n" +
                    "        b.ext1 AS \"userInfo.ext1\",\n" +
                    "        b.ext3 AS \"userInfo.ext3\"\n" +
                    "        FROM USER a\n" +
                    "        LEFT JOIN user_info b ON a.id = b.ext3\n" +
                    "        LEFT JOIN user_role c ON c.user_id = a.id\n" +
                    "        LEFT JOIN regions g ON b.province = g.region_id\n" +
                    "        LEFT JOIN regions e ON b.city = e.region_id\n" +
                    "        LEFT JOIN regions f ON b.district = f.region_id\n" +
                    "        WHERE b.agent_name IS NOT NULL AND b.district IS NOT NULL\n" +
                    "        AND c.role_id = 'a1'\n" +
                    "        \n" +
                    "        ) AS t\n" +
                    "        WHERE t.del_flag = 0  \n" +
                    "       ";

        }
        List list = daochu(sql);
        for (int i = 0; i < list.size(); i++) {
            map.put("" + (i + 1) + "-0", ((Map) list.get(i)).get("login_name"));//代理商账号

            map.put("" + (i + 1) + "-1", ((Map) list.get(i)).get("name"));//代理联系人

            map.put("" + (i + 1) + "-2", ((Map) list.get(i)).get("create_date"));//创建时间

            map.put("" + (i + 1) + "-3", ((Map) list.get(i)).get("tel"));//联系方式

            String f1 = "";
            String f2 = "";
            String f3 = "";//负责辖区拼接

            if (((Map) list.get(i)).get("ext1") != null) {
                f1 = (String) ((Map) list.get(i)).get("ext1");
            } else {
                f1 = "";
            }
            ;//负责辖区

            if (((Map) list.get(i)).get("ext2") != null) {
                f2 = (String) ((Map) list.get(i)).get("ext2");
            } else {
                f2 = "";
            }
            ;//负责辖区

            if (((Map) list.get(i)).get("ext3") != null) {
                f3 = (String) ((Map) list.get(i)).get("ext3");
            } else {
                f3 = "";
            }
            ;//负责辖区

            String fzxq = f1 + f2 + f3;


            map.put("" + (i + 1) + "-4", fzxq);//负责辖区

            map.put("" + (i + 1) + "-5", ((Map) list.get(i)).get("userInfo.ext2"));//商家数量

            int num1 = (Integer) ((Map) list.get(i)).get("del_flag");//状态

            if (num1 == 0) {//状态

                map.put("" + (i + 1) + "-6", ("正常"));//状态

            } else {
                map.put("" + (i + 1) + "-6", ("停用"));//状态

            }


        }
        String xlsName = "代理商管理";
        ToolsDaochu.getTfAttenceListExport(req, res, xlsName, map);
    }


}